# sql_markdown.py
# usage: python ./src/vector/sql_markdown.py

import os
import sys
import json

project_root = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
sys.path.insert(0, project_root)
from config import project_config

# 配置路径
JSON_FILE = 'tmp_question2sql.json'
SQL_OUTPUT_DIR = project_config.sql_output_path  # './sql_output/'

def read_json_file(file_path):
    """安全读取 JSON 文件，保留原始内容"""
    if not os.path.exists(file_path):
        return None, f"文件不存在"

    try:
        with open(file_path, 'r', encoding='utf-8') as f:
            content = f.read().strip()
            if not content:
                return {}, "文件为空"
            # 尝试解析 JSON
            data = json.loads(content)
            return data, "OK"
    except json.JSONDecodeError as e:
        # 打印错误内容帮助调试
        print(f"❌ JSON 解析失败 {file_path}: {e}")
        try:
            # 再次读取，显示前 200 字符
            with open(file_path, 'r', encoding='utf-8') as f:
                raw = f.read(200)
                print(f"   文件开头内容: {repr(raw)}")
        except:
            pass
        return {}, "JSON 格式错误"
    except Exception as e:
        return None, f"读取异常: {e}"

def main():
    if not os.path.exists(JSON_FILE):
        print(f"错误：找不到文件 {JSON_FILE}")
        return

    if not os.path.exists(SQL_OUTPUT_DIR):
        print(f"错误：输出目录不存在 {SQL_OUTPUT_DIR}")
        return

    try:
        with open(JSON_FILE, 'r', encoding='utf-8') as f:
            line_number = 0
            for line in f:
                line = line.strip()
                if not line:
                    continue
                line_number += 1
                try:
                    data = json.loads(line)
                except json.JSONDecodeError as e:
                    print(f"第 {line_number} 行 JSON 解析错误：{e}")
                    continue

                tables = data.get("table", [])
                questions = data.get("questions", [])

                if not tables:
                    print(f"第 {line_number} 行：缺少 'table' 字段或为空。")
                    continue

                if not questions:
                    print(f"第 {line_number} 行：没有 questions，跳过。")
                    continue

                cleaned_questions = [q.replace('\n', ' ').replace('\r', ' ').strip() for q in questions]
                cleaned_questions = list(dict.fromkeys(cleaned_questions))  # 去重

                for table_name in tables:
                    json_file_path = os.path.join(SQL_OUTPUT_DIR, f"{table_name}.json")

                    # 读取现有内容
                    content, msg = read_json_file(json_file_path)
                    if content is None:
                        print(f"⚠️ 跳过 {json_file_path}：{msg}")
                        continue

                    print(f"🔍 当前读取 {json_file_path} 内容：{list(content.keys())}")  # 调试：打印字段名

                    # 获取已有问题并去重
                    existing_questions_set = set(q.replace('\n', ' ').strip() for q in content.get("questions", []))
                    new_questions = [q for q in cleaned_questions if q not in existing_questions_set]

                    # 合并问题
                    all_questions = content.get("questions", []) + new_questions
                    content["questions"] = all_questions

                    # 写回文件
                    try:
                        with open(json_file_path, 'w', encoding='utf-8') as f:
                            json.dump(content, f, ensure_ascii=False, indent=2)
                        print(f"✅ 已将 {len(new_questions)} 个新问题添加到: {json_file_path}")
                    except Exception as e:
                        print(f"写入文件失败 {json_file_path}: {e}")

    except Exception as e:
        print(f"文件读取错误：{e}")
        return

    print("🎉 所有处理完成。")

if __name__ == '__main__':
    main()